﻿//Copyright (c) 2010 William Liu, Enoch Ye
//William Liu <william.lyz@gmail.com>
//Enoch Ye <moonlight.enoch@gmail.com>

//   Licensed under the Apache License, Version 2.0 (the "License");
//   you may not use this file except in compliance with the License.
//   You may obtain a copy of the License at

//       http://www.apache.org/licenses/LICENSE-2.0

//   Unless required by applicable law or agreed to in writing, software
//   distributed under the License is distributed on an "AS IS" BASIS,
//   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
//   See the License for the specific language governing permissions and
//   limitations under the License.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
using GBG_DBM.GlobalParameters;
using System.Windows.Forms;

namespace GBG_DBM.Support.DAL
{
    public static class DBHelper
    {
        #region FIELDS

        private static string _connectionString;
        private static string _connectionStringExclusive;
        private static OleDbConnection _dbConnection;
        private static string _password;

        #endregion

        #region PROPERTIES

        public static string ConnectionString { get { return _connectionString; } }
        public static string ConnectionStringExclusive { get { return _connectionStringExclusive; } }
        public static string CurrentPassword { get { return _password; } }

        #endregion

        #region CONSTRUCTORS
        #endregion

        #region METHODS

        /// <summary>
        /// set the password for database connection
        /// can use to update the password
        /// </summary>
        /// <param name="PW">the password for database connection</param>
        public static void SetPassword(string PW)
        {
            _password = PW;
            DBHelper.UpdateConStr(_password);
            //return PW;
        }

        //Database Connection
        public static OleDbConnection GetDBConnection
        {
            get
            {
                _connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + GlobalParams.DBPath + ";Jet OLeDb:DataBase Password=" + _password;
                _dbConnection = new OleDbConnection(_connectionString);

                if (_dbConnection == null)
                {
                    try
                    {
                        _dbConnection.Open();
                    }
                    catch (OleDbException _OleErr)
                    {
                        throw new Exception(_OleErr.Message);
                    }
                }
                //else if (_dbconnection.state == connectionstate.closed)
                //{
                //    try
                //    {
                //        _dbconnection.open();
                //    }
                //    catch (oledbexception _oleerr)
                //    {
                //        throw new exception(_oleerr.message);
                //    }
                //}
                else if (_dbConnection.State == ConnectionState.Broken)
                {
                    try
                    {
                        _dbConnection.Close();
                        _dbConnection.Open();
                    }
                    catch (OleDbException _OleErr)
                    {
                        throw new Exception(_OleErr.Message);
                    }
                }
                return _dbConnection;
            }
        }

        //Executes an SQL statement against the Connection and returns the number of rows affected
        public static int AffectRows(string sql)
        {
            OleDbCommand _command = new OleDbCommand(sql, _dbConnection);
            int result = _command.ExecuteNonQuery();
            return result;
        }

        //get the first column of the first row in the result set returned by the query
        public static int GetScalar(string sql)
        {
            OleDbCommand _command = new OleDbCommand(sql, _dbConnection);
            int result = int.Parse(_command.ExecuteScalar().ToString());
            return result;
        }

        //get data from database
        public static DataTable GetDataSet(string sql)
        {
            DataSet _dataSet = new DataSet();
            OleDbCommand _command = new OleDbCommand(sql, _dbConnection);
            OleDbDataAdapter _dataAdapter = new OleDbDataAdapter(_command);
            _dataAdapter.Fill(_dataSet);
            return _dataSet.Tables[0];
        }

        /// <summary>
        /// Get a data reader
        /// </summary>
        /// <param name="sql">SQL Query</param>
        /// <returns></returns>
        public static OleDbDataReader GetDataReader(string sql)
        {
            OleDbCommand _command = new OleDbCommand(sql, _dbConnection);
            OleDbDataReader _dataReader = _command.ExecuteReader();
            return _dataReader;
        }

        /// <summary>
        /// update connection string password
        /// </summary>
        /// <param name="PW">Database Password</param>
        private static void UpdateConStr(string PW)
        {
            _connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + GlobalParams.DBPath+";Jet OLeDb:DataBase Password=" + _password;
            _connectionStringExclusive = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + GlobalParams.DBPath + ";Mode=Share Exclusive;Jet OLeDb:DataBase Password=" + _password;
        }

        #endregion
    }
}
